# CPlateSys Database Initialization Script
# This script creates the CPlateSys database and initializes the User table
# Designed to be extensible for future field modifications

param(
    [string]$RootPassword = "123456",
    [string]$DatabaseName = "CPlateSys"
)

function Invoke-MySQLCommand {
    param(
        [string]$Command,
        [string]$Database = ""
    )
    $args = @("-u", "root", "-p$RootPassword")
    if ($Database) { $args += "-D"; $args += $Database }
    $args += "-e"; $args += $Command
    & "C:\Program Files\MySQL\MySQL Server 9.3\bin\mysql.exe" @args
}

# 创建数据库
Invoke-MySQLCommand "CREATE DATABASE IF NOT EXISTS `"$DatabaseName`" CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"


# 创建User表
$createTable = @"
CREATE TABLE IF NOT EXISTS `User` (
    `Id` INT AUTO_INCREMENT PRIMARY KEY,
    `Username` VARCHAR(20) NOT NULL UNIQUE,
    `Password` VARCHAR(64) NOT NULL,
    `JobNumber` VARCHAR(20) NOT NULL UNIQUE,
    `FullName` VARCHAR(100),
    `Role` INT DEFAULT 0,
    `IsActive` BIT DEFAULT 1,
    `CreatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `UpdatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `LastLoginAt` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"@
Invoke-MySQLCommand $createTable $DatabaseName

# 插入超级管理员用户（用户名: SuperAdmin, 密码: admin, 角色: SuperAdmin）
# 先计算admin的SHA256
$adminPassword = "admin"
$sha256 = [System.Security.Cryptography.SHA256]::Create()
$bytes = [System.Text.Encoding]::UTF8.GetBytes($adminPassword)
$hash = $sha256.ComputeHash($bytes)
$adminPasswordHash = [BitConverter]::ToString($hash) -replace "-", ""
$adminPasswordHashBase64 = [Convert]::ToBase64String($hash)

$insertAdmin = @"
INSERT INTO `User` (Username, Password, JobNumber, FullName, Role, IsActive)
SELECT 'SuperAdmin', '$adminPasswordHashBase64', '0000', '超级管理员', 2, TRUE
WHERE NOT EXISTS (SELECT 1 FROM `User` WHERE Username = 'SuperAdmin');
"@
Invoke-MySQLCommand $insertAdmin $DatabaseName

# 创建MotherShips表
$createMotherShipTable = @"
CREATE TABLE IF NOT EXISTS `MotherShips` (
    `Id` INT AUTO_INCREMENT PRIMARY KEY,
    `ShipNo` VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"@
Invoke-MySQLCommand $createMotherShipTable $DatabaseName

# 创建StreamShips表
$createStreamShipTable = @"
CREATE TABLE IF NOT EXISTS `StreamShips` (
    `Id` INT AUTO_INCREMENT PRIMARY KEY,
    `ShipNo` VARCHAR(20) NOT NULL UNIQUE,
    `MotherShipId` INT NOT NULL,
    FOREIGN KEY (`MotherShipId`) REFERENCES `MotherShips`(`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"@
Invoke-MySQLCommand $createStreamShipTable $DatabaseName

# 创建PlateAnnotationInfo表
$createPlateAnnotationInfoTable = @"
CREATE TABLE IF NOT EXISTS `PlateAnnotationInfo` (
    `Id` INT AUTO_INCREMENT PRIMARY KEY,
    `MotherShipNo` VARCHAR(20),
    `StreamShipNo` VARCHAR(20),
    `LotNo` VARCHAR(20),
    `PlateNo` VARCHAR(20),
    `Sequence` VARCHAR(20),
    `CNO` VARCHAR(20),
    `DesignSpecCls` VARCHAR(50),
    `DesignSpecThickness` DOUBLE,
    `DesignSpecLength` DOUBLE,
    `DesignSpecWidth` DOUBLE,
    `SheetConsistentNo` VARCHAR(50),
    `SheetSpecCls` VARCHAR(50),
    `SheetSpecThickness` DOUBLE,
    `SheetSpecLength` DOUBLE,
    `SheetSpecWidth` DOUBLE,
    `LayoutMean` INT,
    `MergedInfo` VARCHAR(100),
    `LayoutInfo` TEXT COMMENT '布局信息JSON字符串',
    `PlanUsedDate` DATETIME NULL,
    `IsAnnotationGenerated` BIT DEFAULT 0,
    `DesignDrawingPath` VARCHAR(200),
    `AnnotationDrawingPath` VARCHAR(200),
    `SourceType` INT DEFAULT 0,
    
    -- 添加索引以提高查询性能
    INDEX `idx_stream_ship_lot_sequence` (`StreamShipNo`, `LotNo`, `Sequence`),
    INDEX `idx_mother_ship_no` (`MotherShipNo`),
    INDEX `idx_plate_no` (`PlateNo`),
    INDEX `idx_cno` (`CNO`),
    INDEX `idx_plan_used_date` (`PlanUsedDate`),
    INDEX `idx_source_type` (`SourceType`),
    INDEX `idx_layout_mean` (`LayoutMean`),
    INDEX `idx_is_annotation_generated` (`IsAnnotationGenerated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"@
Invoke-MySQLCommand $createPlateAnnotationInfoTable $DatabaseName

# 创建GlobalConfig表
$createGlobalConfigTable = @"
CREATE TABLE IF NOT EXISTS `GlobalConfig` (
    `Id` INT AUTO_INCREMENT PRIMARY KEY,
    `ConfigKey` VARCHAR(64) NOT NULL UNIQUE,
    `ConfigValue` TEXT NOT NULL,
    `Description` VARCHAR(255),
    `UpdatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `UpdatedBy` VARCHAR(64)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"@
Invoke-MySQLCommand $createGlobalConfigTable $DatabaseName

# 插入默认配置
$insertRoundingRule = @"
INSERT INTO `GlobalConfig` (ConfigKey, ConfigValue, Description, UpdatedBy)
VALUES (
    'RoundingRule',
    '{"default":"4舍5入","stages":{"3AP":"4舍5入","4PS":"2舍3入"}}',
    '加工阶段取整规则',
    'SuperAdmin'
)
ON DUPLICATE KEY UPDATE ConfigValue=VALUES(ConfigValue), Description=VALUES(Description), UpdatedBy=VALUES(UpdatedBy), UpdatedAt=CURRENT_TIMESTAMP;
"@
Invoke-MySQLCommand $insertRoundingRule $DatabaseName

$insertLayerColor = @"
INSERT INTO `GlobalConfig` (ConfigKey, ConfigValue, Description, UpdatedBy)
VALUES (
    'LayerColor',
    '{"lineLayerColor":"#00FF00","textLayerColor":"#FFA500","infoLayerColor":"#0000FF"}',
    '图层颜色配置',
    'SuperAdmin'
)
ON DUPLICATE KEY UPDATE ConfigValue=VALUES(ConfigValue), Description=VALUES(Description), UpdatedBy=VALUES(UpdatedBy), UpdatedAt=CURRENT_TIMESTAMP;
"@
Invoke-MySQLCommand $insertLayerColor $DatabaseName

$insertOutputDirectory = @"
INSERT INTO `GlobalConfig` (ConfigKey, ConfigValue, Description, UpdatedBy)
VALUES (
    'OutputDirectory',
    '{"outputPath":"\\\\\\\\192.168.1.2\\\\share\\\\C-plate\\\\","enableBackup":true,"backupRetentionDays":30}',
    '输出目录配置',
    'SuperAdmin'
)
ON DUPLICATE KEY UPDATE ConfigValue=VALUES(ConfigValue), Description=VALUES(Description), UpdatedBy=VALUES(UpdatedBy), UpdatedAt=CURRENT_TIMESTAMP;
"@
Invoke-MySQLCommand $insertOutputDirectory $DatabaseName

$insertInterfaceConfig = @"
INSERT INTO `GlobalConfig` (ConfigKey, ConfigValue, Description, UpdatedBy)
VALUES (
    'InterfaceConfig',
    '{"cadWinSharePath":"\\\\\\\\192.168.1.100\\\\CadWinShare\\\\","kmacsApiUrl":"http://192.168.1.101:8080/api/","timeoutSeconds":30,"enableCache":true,"cacheExpirationMinutes":10}',
    '接口配置',
    'SuperAdmin'
)
ON DUPLICATE KEY UPDATE ConfigValue=VALUES(ConfigValue), Description=VALUES(Description), UpdatedBy=VALUES(UpdatedBy), UpdatedAt=CURRENT_TIMESTAMP;
"@
Invoke-MySQLCommand $insertInterfaceConfig $DatabaseName

# 为已存在的PlateAnnotationInfo表添加LayoutInfo字段（如果不存在）
$addLayoutInfoColumn = "ALTER TABLE `PlateAnnotationInfo` ADD COLUMN `LayoutInfo` TEXT COMMENT '布局信息JSON字符串';"
Invoke-MySQLCommand $addLayoutInfoColumn $DatabaseName

# 忽略字段已存在的错误
if ($LASTEXITCODE -ne 0) {
    Write-Host "LayoutInfo字段可能已存在，继续执行..." -ForegroundColor Yellow
}

Write-Host "数据库初始化完成！" -ForegroundColor Green
Write-Host "PlateAnnotationInfo表已包含LayoutInfo字段，支持布局功能。" -ForegroundColor Cyan